package com.cl.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.parsing.Problem;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.cl.entity.*;
import com.cl.service.payGrant.PayGrantExcelService;

import org.hibernate.Session; 
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import java.sql.*;

@Repository
@Transactional(value = "transactionManager", noRollbackFor = { NoResultException.class })
public class SearchDao <T> {

	@PersistenceContext(unitName = "entityManagerFactory")
	private EntityManager entityManager;
	
	public int getEntityTotal(List<Criterion> criterions, Class<T> entity) {
		Session session = entityManager.unwrap(Session.class);
		Criteria criteria = session.createCriteria(entity);
		criteria.setProjection(Projections.rowCount());
		if (criterions != null && criterions.size() > 0) {
			for (Criterion criterion:criterions) {
				criteria.add(criterion);
			}
		}
		return ((Long) criteria.uniqueResult()).intValue();
	}
	
	public T getEntity(Class<T> entityClass, Object kval) {
		return entityManager.find(entityClass, kval);
	}

	@SuppressWarnings("unchecked")
	public List<T> listEntities(String entity_name, String property_name, String kw, Integer limit) {
		Session session = entityManager.unwrap(Session.class);
		Criteria c = session.createCriteria(entity_name);
		c.addOrder(Order.asc(property_name));
		if(kw!=null && kw.trim().endsWith("")){
			c.add(Restrictions.like(property_name, kw.trim()+"%"));
		}
		if(limit!=null){
			c.setFirstResult(0).setMaxResults(limit);
		}
		return c.list();
	}	
	
	@SuppressWarnings("unchecked")
	public List<Object> disticnt(Class<T> entity, String property_name, String kw, Integer limit,List<Criterion> criterions) {
		Session session = entityManager.unwrap(Session.class);
		Criteria c = session.createCriteria(entity);
		c.setProjection(Projections.distinct(Projections.property(property_name)));
		c.addOrder(Order.asc(property_name));
		if (criterions != null && criterions.size() > 0) {
			for (Criterion criterion:criterions) {
				c.add(criterion);
			}
		}
		
		if(limit!=null){
			c.setFirstResult(0).setMaxResults(limit);
		}
		return c.list();
	}
		
	@SuppressWarnings("unchecked")
	public List<T> getEntities(int start, int limit, List<Criterion> criterions, List<Order> orders, Class<T> entity) {
		Session session = entityManager.unwrap(Session.class);
		Criteria criteria = session.createCriteria(entity);
		if (criterions != null && criterions.size() > 0) {
			for (Criterion criterion:criterions) {
				criteria.add(criterion);
			}
		}
		if (orders != null && orders.size() > 0) {
			for (Order order:orders) {
				criteria.addOrder(order);
			}
		}
		return criteria.setFirstResult(start).setMaxResults(limit).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<T> getAllEntities(List<Criterion> criterions, List<Order> orders, Class<T> entity) {
		Session session = entityManager.unwrap(Session.class);
		Criteria criteria = session.createCriteria(entity);
		if (criterions != null && criterions.size() > 0) {
			for (Criterion criterion:criterions) {
				criteria.add(criterion);
			}
		}
		if (orders != null && orders.size() > 0) {
			for (Order order:orders) {
				criteria.addOrder(order);
			}
		}
		return criteria.list();
	}
	
	/**
	 * 
	 * @param clazz
	 * @param className
	 * @param id1
	 * @param entity1
	 * @param entity2
	 * @param idProperty
	 * @return
	 */
	public List<T> getRelEntities(Class<T> clazz, String className, Long id1, String entity1, String entity2, String idProperty) {
		String hql = "select m from " 
	               + className 
	               + " m, Rel r where r.entity1=:entity1 and r.entity2=:entity2 and r.id2 = m." 
	               + idProperty 
	               + " and r.id1=:id1 order by r.ord asc";
	    return entityManager.createQuery(hql, clazz)
	    		   .setParameter("id1", id1)
	    		   .setParameter("entity1", entity1)
	    		   .setParameter("entity2", entity2)
	               .getResultList();
	}

	public List<Rel> getRels(Rel rel) {
		String hql = "from Rel where id1=:id1 and id2 =:id2 and entity1=:entity1 and entity2=:entity2";
	    return entityManager.createQuery(hql, Rel.class)
	    		.setParameter("id1", rel.getId1())
	    		.setParameter("entity1", rel.getEntity1())
	    		.setParameter("id2", rel.getId2())
	    		.setParameter("entity2", rel.getEntity2())
	    		.getResultList();
	}
	
	public List<Rel> getAllRels(String entity, Object value) {
		String hql = "from Rel where (id1=:id1 and entity1=:entity1) or (id2=:id2 and entity2=:entity2)";
	    return entityManager.createQuery(hql, Rel.class)
	    		.setParameter("id1", value)
	    		.setParameter("entity1", entity)
	    		.setParameter("id2", value)
	    		.setParameter("entity2", entity)
	    		.getResultList();
	}

	public UUser getUserByUid(String loginName) {
		//c4w 外单位人员中存在与本系统用户同姓名的情况
		String hql = "from UUser where name like :loginName and ugroup is not null";
		try {
		    return entityManager.createQuery(hql, UUser.class)
		    		.setParameter("loginName", "%" + loginName + "%")
		    		.getSingleResult();
		} catch (Exception e) {
			e.printStackTrace();
		    return null;
		}
	}
	public List<UUser> listUsers(String kw,int limit){
		String hql = "from UUser where gid>0";
		if(kw!=null)
			hql+=" and name like '%"+kw+"%'";
		return entityManager.createQuery(hql, UUser.class).setMaxResults(limit).getResultList();
	}
	/**
	 * 
	 * @param id
	 * @return
	 */
	public UGroup getUserGroupByID(Long id) {
		String hql = "from UGroup where id =:id";
		try {
		    return entityManager.createQuery(hql, UGroup.class)
		    		.setParameter("id", id)
		    		.getSingleResult();
		} catch (NoResultException e) {
		      return null;
		}
	}
	
	
	public TokenRel getTokenRelByToken(String token) {
		String hql = "from TokenRel where tokenKey =:token";
		try {
		    return entityManager.createQuery(hql, TokenRel.class)
		    		.setParameter("token", token)
		    		.getSingleResult();
		} catch (NoResultException e) {
		      return null;
		}
	}
	
	public AccessToken getAccessTokenByToken(String token) {
		String hql = "select A from AccessToken A,TokenRel B where A.id = B.tokenValue and B.tokenKey =:token ";
		try {
		    return entityManager.createQuery(hql, AccessToken.class)
		    		.setParameter("token", token)
		    		.getSingleResult();
		} catch (NoResultException e) {
		      return null;
		}
	}
	
	/**
	 * 返回image表的cdlabel, imgFile字段用于去重
	 * @return
	 */
	public List<Object[]> listImagesInfo(){
		String hql = "select cdlabel,imgFile  from Image";
		return entityManager.createQuery(hql).getResultList();
	}

	/**
	 *
	 * @return
	 */
	public Integer getOrgMaxStatus() {
		String hql = "select max(status) from UOrg";
		try {
			return entityManager.createQuery(hql, Integer.class).getSingleResult()==null?1:entityManager.createQuery(hql, Integer.class).getSingleResult();
		} catch (NoResultException e) {
			return 1;
		}
	}

	/**
	 *
	 * @param status
	 * @return
	 */
	public int getOrgCountToDel(int status) {
		String hql = "select count(*) from UOrg where status=:status";
		try {
			return entityManager.createQuery(hql, Long.class)
					.setParameter("status", status)
					.getSingleResult().intValue();
		} catch (NoResultException e) {
			return 0;
		}
	}

	/**
	 * 通过身份证号获取用户
	 * @param userId
	 * @return
	 */
	public UUser getUserById(String userId){
		String hql = "from UUser where userId=:userId";
		try {
			return entityManager.createQuery(hql, UUser.class)
					.setParameter("userId", userId)
					.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}

	public UOrg getOrgById(String orgId){
		String hql = "from UOrg where orgid=:orgid";
		try {
			return entityManager.createQuery(hql, UOrg.class)
					.setParameter("orgid", orgId)
					.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	public List<UOrg> getAllUOrgs(String entity) {
		String hql = "from UOrg where orgname=:orgname";
	    return entityManager.createQuery(hql, UOrg.class)
	    		.setParameter("orgname", entity)
	    		.getResultList();
	}
	
	
	public List<UUser> getorguser(String entity1) {
		List<UUser> orguser = new ArrayList<UUser>();
		String sql= "select userId,name from UUser where orgName='资料信息部'";
		
		try {
			orguser = entityManager.createQuery(sql).getResultList();
		} catch (NoResultException e) {
		}
		
		return orguser;
	}

	
	public List<BaseAuthor> getAllAuthors() {
		return entityManager.createQuery("from BaseAuthor", BaseAuthor.class).getResultList();
	}
	public List<T> find(String hql, Class<T> cls){
		return entityManager.createQuery(hql, cls).getResultList();
	}
/*	
	*//**
	 * 查找某类某个实体对应的未发放稿酬记录，支持类名的模糊查询
	 * 
	 *//*
	public List<CcPay> getNotGivenRelPays(String entity, Object value) {
		String hql = "from CcPay where reGiveFlag!='Y' and class_ like :class and keyval=:keyval";
	    return entityManager.createQuery(hql, CcPay.class)
	    		.setParameter("class", '%'+ entity+'%')
	    		.setParameter("keyval", (Long)value)
	    		.getResultList();
	}*/

	/**
	 * 获取工具量
	 * @param jpql
	 * @return　工作量统计
	 */
	public List<WorkLoad> getWorkLoad(String jpql) {
		List<WorkLoad> workload = new ArrayList<WorkLoad>();
		String hql = "select new Map(userid as userName,count(userid) as num) from CcSync where oper='A' "+jpql + " group by userid order by userid";
		List list = null;
		try {
			list = entityManager.createQuery(hql).getResultList();
		} catch (NoResultException e) {
		}
		if (list != null && list.size() > 0) {
			for (int i=0;i<list.size();i++) {
				Map map = (Map)list.get(i);
				if (map != null) {
					WorkLoad obj = new WorkLoad();
					obj.id = i + 1L;
					obj.userName = (String)map.get("userName");
					obj.num = (Long)map.get("num");
					workload.add(obj);
				}
			}
		}
		return workload;
	}
	
	/**
	 * 根据type获取稿酬因子列表，用于稿酬计算
	 */
	public List<BasePayfactor> getPayfactorListByType(String type){
		String hql = "from BasePayfactor A where (A.type= :type or A.type is null or A.type='0') order by A.type,A.ord asc";
		 return entityManager.createQuery(hql, BasePayfactor.class)
				 .setParameter("type", type)
				 .getResultList();
	}
	

	/**
	 * 根据部门获取用户，用于稿酬统计
	 */
	public List<UUser> listOrgUsers(String org,int limit){
		String hql = "from UUser where identificationUnit='院内'";
		if(org!=null)
			hql+="and orgName='资料信息部'";
		return entityManager.createQuery(hql, UUser.class).setMaxResults(limit).getResultList();

	}

	/**
	 * 查找某类某个实体对应的未发放稿酬记录，支持类名的模糊查询
	 * 
	 */
	public List<CcPay> getRelPays(String entity, Object value) {
		String hql = "from CcPay where class_ like :class and keyval=:keyval and reGiveFlag!='Y'";
	    return entityManager.createQuery(hql, CcPay.class)
	    		.setParameter("class", '%'+ entity+'%')
	    		.setParameter("keyval", (Long)value)
	    		.getResultList();
	}
	
	
	/**
	 * 查找某类某个实体对应的稿酬记录，支持类名的模糊查询
	 * 
	 */
	public CcPay getRelPay(String entity, Object value, String workitem) {
		String hql = "from CcPay where class_ like :class and keyval=:keyval and workitem=:workitem";
	    return entityManager.createQuery(hql, CcPay.class)
	    		.setParameter("class", '%'+ entity+'%')
	    		.setParameter("keyval", (Long)value)
	    		.setParameter("workitem", workitem)
	    		.getSingleResult();
	}
	

	
	/**
	 * 查找稿酬发放记录
	 * @throws Exception 
	 * 
	 */
	public List<CcPay> getPayGrantsList(Date begin, Date end) throws Exception {
		//String hql1 = "from CcPay where class_ like :class and keyval=:keyval";
		//String hql = "from CcPay  where rewardCTime between :begin and :end  and reGiveFlag='N' and rewardFlag='Y' ";
		//17-06-08 将时间范围从确认时间修改为稿酬创建时间
		String hql = "from CcPay  where created between :begin and :end  and reGiveFlag='N' and rewardFlag='Y' and rewardCTime is NOT NULL";
		//c4w 如果存在author为空，拒绝执行
		String hql_author = "select count(*) " + hql + " and authorId is NULL"; 
		int cout_author =  entityManager.createQuery(hql_author, Long.class)
				.setParameter("begin",begin )
	    		.setParameter("end", end)
				.getSingleResult().intValue();
		if(cout_author>0){
			throw new Exception("存在【"+cout_author+"】条当事人为空的稿酬记录，请核实");
		}
		String hql_cout = "select count(*) " + hql; 
		int cout =  entityManager.createQuery(hql_cout, Long.class)
				.setParameter("begin",begin )
	    		.setParameter("end", end)
				.getSingleResult().intValue();
		if(cout==0){
			throw new Exception("无待处理稿酬");
		}
		//超出最大数目限制，抛异常，拒绝执行
		if( false && cout>PayGrantExcelService.PAY_COUNT_MAX){
			throw new Exception("待处理稿酬【"
					+cout+"】条超过每次能够处理的最大条数【"
					+PayGrantExcelService.PAY_COUNT_MAX
					+"】,<br>请缩小范围重新尝试");
		}
	    return entityManager.createQuery(hql, CcPay.class)
	    		.setParameter("begin",begin )
	    		.setParameter("end", end)
	    		.setMaxResults(PayGrantExcelService.PAY_COUNT_MAX)
	    		.getResultList();
	}

	public int getMaxOrd(Long id1, String entity1, String entity2) {
		String hql = "select max(ord) from Rel where entity1=:entity1 and entity2=:entity2 and id1 = :id1";
		try {
			BigDecimal obj = entityManager.createQuery(hql, BigDecimal.class)
					.setParameter("id1", id1)
					.setParameter("entity1", entity1)
					.setParameter("entity2", entity2)
					.getSingleResult();
			return obj==null?0:obj.intValue();
		} catch (NoResultException e) {
			return 0;
		}
	}

	/**
	 *
	 * @param associatedName
	 * @param associatedID
	 * @param relatedName
	 * @param targetObj
	 * @return
	 */
	public Rel getRelOrder(String associatedName, Long associatedID, String relatedName, Long targetObj) {
		String hql = "from Rel where entity1=:associatedName and entity2=:relatedName and id1=:associatedID and id2=:targetObj";
		try {
			return entityManager.createQuery(hql, Rel.class)
					.setParameter("associatedName", associatedName)
					.setParameter("associatedID", associatedID)
					.setParameter("relatedName", relatedName)
					.setParameter("targetObj", targetObj)
					.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}

	public List<Rel> getRelsOrder(String associatedName, Long associatedID, String relatedName, List<Long> targetObj) {
		String hql = "from Rel where entity1=:associatedName and entity2=:relatedName and id1=:associatedID and id2 in(:targetObj) order by ord";
		try {
			return entityManager.createQuery(hql, Rel.class)
					.setParameter("associatedName", associatedName)
					.setParameter("associatedID", associatedID)
					.setParameter("relatedName", relatedName)
					.setParameter("targetObj", targetObj)
					.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}

	public List<Rel> getRelsSpaceOrder(String associatedName, Long associatedID, String relatedName, BigDecimal minOrder, BigDecimal maxOrder) {
			BigDecimal maxOrd = maxOrder.subtract(minOrder).intValue()>0?maxOrder:minOrder;
			BigDecimal minOrd = maxOrder.subtract(minOrder).intValue()>0?minOrder:maxOrder;
			String hql = "from Rel where entity1=:associatedName and entity2=:relatedName and id1=:associatedID and ord<:maxOrder and ord>:minOrder order by ord";
			try {
				return entityManager.createQuery(hql, Rel.class)
						.setParameter("associatedName", associatedName)
						.setParameter("associatedID", associatedID)
						.setParameter("relatedName", relatedName)
						.setParameter("maxOrder", maxOrd)
						.setParameter("minOrder", minOrd)
						.getResultList();
			} catch (NoResultException e) {
				return null;
			}
	}
	
	public List<UUser> getInsideUser() {
		String hql = "from UUser where identificationUnit='院内'";
		try {
			return entityManager.createQuery(hql, UUser.class).getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	/**
	 * 
	 * @param clazz
	 * @param className
	 * @param id1
	 * @param entity1
	 * @param entity2
	 * @param idProperty
	 * @return
	 */
	public List<CcPay> getRelPaysFromRel(Long id1, String entity1) {
		String hql = "select m from CcPay m, Rel r where r.entity1=:entity1 and r.entity2='CCPAY' and r.id2 = m.id and m.reGiveFlag!='Y' and r.id1=:id1 order by r.ord";
	    return entityManager.createQuery(hql, CcPay.class)
	    		   .setParameter("id1", id1)
	    		   .setParameter("entity1", entity1)
	               .getResultList();
	}

	public List<T> getEntityList(Class<T> cls) {
		String hql = "from "+ cls.getName();
	    return entityManager.createQuery(hql, cls)
	               .getResultList();
	}
	
	/**
	 * 通过userDn获取user对象
	 * @param userDn
	 * @return
	 */
	public UUser getUserByDn(String userDn) {
	    try {
	    	return entityManager.createQuery("from UUser where userdn=:userDn", UUser.class)
	    			.setParameter("userDn", userDn).getSingleResult();
	    } catch (NoResultException e) {
	    	return null;
	    }
	}
	
}
